package com.yunhe.abnormal.repository.data;

import com.yunhe.abnormal.domain.data.AlarmEvent;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

/**
 * 告警事件数据仓库
 *
 * @author duanxiaoyong
 * @desc 告警事件数据仓库定义
 */
@Repository
public interface AlarmEventRepository extends JpaRepository<AlarmEvent, Long> {

    /**
     * 查询全部事件对象
     *
     * @param spec     查询条件
     * @param pageable 分页对象
     */
    Page<AlarmEvent> findAll(Specification<AlarmEvent> spec, Pageable pageable);

    /**
     * 查询全部事件对象
     *
     * @param spec 查询条件
     * @param sort 排序对象
     */
    Iterable<AlarmEvent> findAll(Specification<AlarmEvent> spec, Sort sort);

    /**
     * 查询全部事件对象
     *
     * @param spec 查询条件
     */
    Iterable<AlarmEvent> findAll(Specification<AlarmEvent> spec);

    @Transactional
    @Modifying
    @Query(value = "delete from data_alarm_event where station_id=:stationId", nativeQuery = true)
    void deleteAlarmEventsByStationId(Long stationId);

    /**
     * 根据条件查找事件
     *
     * @param devId            设备代码
     * @param alarmTypeId      告警类型Id
     * @param abnormalStatusId 告警状态Id
     */
    @Query(value = "select * from data_alarm_event a where a.dev_id=:devId and a.alarm_type_id=:alarmTypeId and a.abnormal_status_id=:abnormalStatusId order by a.id desc", nativeQuery = true)
    Iterable<AlarmEvent> findEvents(@Param("devId") Long devId,
                                    @Param("alarmTypeId") Long alarmTypeId,
                                    @Param("abnormalStatusId") Long abnormalStatusId);

    /**
     * 查找某设备最后发生某一类型告警的事件记录
     *
     * @param devId       设备代码
     * @param alarmTypeId 告警类型Id
     */
    @Query(value = "select * from data_alarm_event a where a.dev_id=:devId and a.alarm_type_id=:alarmTypeId " +
            "and a.abnormal_status_name!='Eliminated' order by a.id desc limit 1", nativeQuery = true)
    AlarmEvent findEvents(@Param("devId") Long devId,
                          @Param("alarmTypeId") Long alarmTypeId);

    /**
     * 根据单位和电站查找全部正在告警的事件
     */
    @Query(value = "select * from data_alarm_event a where a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious') order by a.start_time desc", nativeQuery = true)
    Iterable<AlarmEvent> findAllWarningMoreThanModerate();

    @Query(value = "select * from data_alarm_event a where a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious')", nativeQuery = true)
    Page<AlarmEvent> findAllWarningMoreThanModeratePage(Pageable pageable);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationList and a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious') order by a.start_time desc", nativeQuery = true)
    Iterable<AlarmEvent> findAllWarningMoreThanModerate(@Param("stationList") List<Long> stationList);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationList and a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious')", nativeQuery = true)
    Page<AlarmEvent> findAllWarningMoreThanModeratePage(@Param("stationList") List<Long> stationList, Pageable pageable);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationList and a.abnormal_status_name!='Eliminated' order by a.start_time desc", nativeQuery = true)
    Iterable<AlarmEvent> findAllWarning(@Param("stationList") List<Long> stationList);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationList and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    Page<AlarmEvent> findAllWarningPage(@Param("stationList") List<Long> stationList, Pageable pageable);

//    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious') order by a.start_time desc", nativeQuery = true)
//    Iterable<AlarmEvent> findAllWarningMoreThanModerate(@Param("firmId") Long firmId);
//
//    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name!='Eliminated' and (a.alarm_level_name='Moderate' or a.alarm_level_name='Serious')", nativeQuery = true)
//    Page<AlarmEvent> findAllWarningMoreThanModeratePage(@Param("firmId") Long firmId, Pageable pageable);
//
//    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name!='Eliminated' order by a.start_time desc", nativeQuery = true)
//    Iterable<AlarmEvent> findAllWarning(@Param("firmId") Long firmId);
//
//    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
//    Page<AlarmEvent> findAllWarningPage(@Param("firmId") Long firmId, Pageable pageable);

    @Query(value = "select * from data_alarm_event a where a.abnormal_status_name!='Eliminated' order by a.start_time desc", nativeQuery = true)
    Iterable<AlarmEvent> findAllWarning();

    @Query(value = "select * from data_alarm_event a where a.abnormal_status_name!=:eliminatedStatusName", nativeQuery = true)
    List<AlarmEvent> findAllWarningWithoutSort(@Param("eliminatedStatusName") String eliminatedStatusName);

    @Query(value = "select * from data_alarm_event a where a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    Page<AlarmEvent> findAllWarningPage(Pageable pageable);

    /**
     * 根据电站ID获得全部正在告警事件
     *
     * @param stationId
     * @return
     */
    @Query(value = "select * from data_alarm_event a where a.station_id=:stationId and a.abnormal_status_name!='Eliminated' order by a.start_time desc", nativeQuery = true)
    Iterable<AlarmEvent> findWarningByStationId(@Param("stationId") Long stationId);

    /**
     * 根据电站ID获得未消除且不为可忽略等级的告警事件
     *
     * @param stationIds
     * @return
     */
    @Query(value = "select * from data_alarm_event a where a.station_id in :stationIds and a.abnormal_status_name!='Eliminated'" +
            " and a.alarm_level_name != 'Ignore'", nativeQuery = true)
    Iterable<AlarmEvent> findWarningByStationIds(@Param("stationIds") List<Long> stationIds);

    @Query(value = "select * from data_alarm_event a where a.station_id=:stationId and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    Page<AlarmEvent> findWarningByStationIdPage(@Param("stationId") Long stationId, Pageable pageable);

    /**
     * 根据事件号查找事件
     *
     * @param eventNum 事件号
     */
    @Query(value = "select * from data_alarm_event a where a.event_num=:eventNum", nativeQuery = true)
    AlarmEvent findByEventNum(String eventNum);

    /**
     * 查询最后一次添加的事件
     */
    @Query(value = "select * from data_alarm_event a order by a.id desc limit 1", nativeQuery = true)
    AlarmEvent findLastEvent();

    /**
     * 根据工单状态查找事件
     *
     * @param queryStr 查询条件
     */
    @Query(value = "select * from data_alarm_event a inner join data_work_order b where a.id=b.event_id and b.status_title  like CONCAT('%', :queryStr, '%')", nativeQuery = true)
    Iterable<AlarmEvent> findByWorkOrderStatus(@Param("queryStr") String queryStr);

    /**
     * 根据告警状态查找告警事件
     *
     * @param firmId        单位代码
     * @param alarmStatusId 告警状态Id
     */
    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.alarm_status_id=:alarmStatusId", nativeQuery = true)
    Iterable<AlarmEvent> findAlarmEventByAlarmStatusId(@Param("firmId") Long firmId, @Param("alarmStatusId") Long alarmStatusId);

    /**
     * 根据单位、最高告警状态、开始时间查找告警事件
     *
     * @param firmId       单位代码
     * @param abnormalName 告警状态
     * @param time         开始时间
     */
    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name=:abnormalName " +
            "and a.start_time like binary concat('%', :time, '%')", nativeQuery = true)
    Iterable<AlarmEvent> search(@Param("firmId") Long firmId,
                                @Param("abnormalName") String abnormalName,
                                @Param("time") String time);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationId and a.abnormal_status_id=:abnormalStatusId " +
            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByStationIds(@Param("stationId") List<Long> stationId,
                                        @Param("abnormalStatusId") Long abnormalStatusId,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime);

    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_id=:abnormalStatusId " +
            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByFirmId(@Param("firmId") Long firmId,
                                    @Param("abnormalStatusId") Long abnormalStatusId,
                                    @Param("startTime") Date startTime,
                                    @Param("endTime") Date endTime);

    @Query(value = "select * from data_alarm_event a where a.dev_id=:devId and a.abnormal_status_id=:abnormalStatusId " +
            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByDevId(@Param("devId") Long devId,
                                   @Param("abnormalStatusId") Long abnormalStatusId,
                                   @Param("startTime") String startTime,
                                   @Param("endTime") String endTime);

//    @Query(value = "select * from data_alarm_event_test a where a.firm_id=:firmId and a.dev_id=:devId " +
//            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
//    List<AlarmEvent> searchByDevId(@Param("firmId") Long firmId,
//                                     @Param("devId") Long devId,
//                                     @Param("startTime") Date startTime,
//                                     @Param("endTime") Date endTime);

    @Query(value = "select * from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByStationIds(@Param("stationId") List<Long> stationId,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime);

    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByFirmId(@Param("firmId") Long firmId,
                                    @Param("startTime") String startTime,
                                    @Param("endTime") String endTime);

    @Query(value = "select * from data_alarm_event a where a.dev_id=:devId and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> searchByDevId(@Param("devId") Long devId,
                                   @Param("startTime") String startTime,
                                   @Param("endTime") String endTime);

    /**
     * 根据单位、开始时间、结束时间查找告警事件
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     */
    @Query(value = "select * from data_alarm_event o where o.station_id=:stationId and o.start_time>=:startTime and o.start_time<=:endTime", nativeQuery = true)
    Iterable<AlarmEvent> searchAmount(@Param("stationId") Long stationId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**
     * 根据单位、开始时间、结束时间、告警等级查找告警事件
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     */
    @Query(value = "select * from data_alarm_event o where o.station_id=:stationId and o.alarm_level_id=:alarmLevelId and o.start_time>=:startTime and o.start_time<=:endTime", nativeQuery = true)
    Iterable<AlarmEvent> searchAmount(@Param("stationId") Long stationId, @Param("alarmLevelId") Long alarmLevelId, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**
     * 查找正在告警的事件
     */
    @Query(value = "select * from data_alarm_event a where a.firm_id=:firmId and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    List<AlarmEvent> findAlarmEvents(@Param("firmId") Long firmId);

    @Query(value = "select * from data_alarm_event a where a.records = '调试中'", nativeQuery = true)
    List<AlarmEvent> findDebuggingEvent();

    /**
     * 根据单位代码查找告警事件
     *
     * @param firmId 单位代码
     */
    List<AlarmEvent> findByFirmId(Long firmId);

    /**
     * 根据电站代码查找告警事件
     *
     * @param stationId 电站
     */
    List<AlarmEvent> findByStationId(Long stationId);

    /**
     * 根据处理人name查找告警事件
     *
     * @param userId 处理人Id
     */
    List<AlarmEvent> findByUserIdProcess(Long userId);

    /**
     * 根据设备代码查找告警事件
     *
     * @param devId 设备
     */
    List<AlarmEvent> findByDevId(Long devId);

    @Query(value = "select * from data_alarm_event a where a.station_id=:stationId and a.start_time>=:startTime " +
            "and a.start_time<=:endTime", nativeQuery = true)
    List<AlarmEvent> findByStationIdAndDtime(@Param("stationId") Long stationId,
                                             @Param("startTime") Date startTime,
                                             @Param("endTime") Date endTime);

    /**
     * 根据电站ID和告警等级获得当日正在告警的事件
     *
     * @param stationId
     * @param alarmLevelId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select * from data_alarm_event a where a.station_id=:stationId and a.alarm_level_id=:alarmLevelId and a.start_time>=:startTime " +
            "and a.start_time<=:endTime and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    List<AlarmEvent> findWarningDayByStationAndAlarmLevel(@Param("stationId") Long stationId,
                                                          @Param("alarmLevelId") Long alarmLevelId,
                                                          @Param("startTime") Date startTime,
                                                          @Param("endTime") Date endTime);

    /**
     * 根据电站ID和告警类型获得当日正在告警的事件
     *
     * @param stationId
     * @param alarmTypeId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select * from data_alarm_event a where a.station_id=:stationId and a.alarm_type_id=:alarmTypeId and a.start_time>=:startTime " +
            "and a.start_time<=:endTime and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    List<AlarmEvent> findWarningDayByStationAndAlarmType(@Param("stationId") Long stationId,
                                                         @Param("alarmTypeId") Long alarmTypeId,
                                                         @Param("startTime") Date startTime,
                                                         @Param("endTime") Date endTime);

    @Query(value = "select distinct alarm_level_name from data_alarm_event a where a.station_id=:stationId " +
            "and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    List<String> getStationAlarmLevels(@Param("stationId") Long stationId);

    @Query(value = "select distinct alarm_level_name from data_alarm_event a where a.dev_id=:deviceId " +
            "and a.abnormal_status_name!='Eliminated'", nativeQuery = true)
    List<String> getDeviceAlarmLevels(@Param("deviceId") Long deviceId);

    @Query(value = "select * from data_alarm_event a where a.dev_id=:deviceId and a.alarm_status_id in :alarmStatusIds and a" +
            ".abnormal_status_name!='Eliminated'",
            nativeQuery = true)
    List<AlarmEvent> findByDeviceAndAlarmStatus(@Param("deviceId") Long deviceId, @Param("alarmStatusIds") List<Long> alarmStatusIds);

    @Query(value = "select start_time from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a.start_time<=:endTime",
            nativeQuery = true)
    List<String> searchStartTimeByStationIds(@Param("stationId") List<Long> stationId,
                                             @Param("startTime") String startTime,
                                             @Param("endTime") String endTime);

    @Query(value = "select start_time from data_alarm_event a where a.station_id in :stationId and a.abnormal_status_id=:abnormalStatusId " +
            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<String> searchStartTimeByStationIds(@Param("stationId") List<Long> stationId,
                                             @Param("abnormalStatusId") Long abnormalStatusId,
                                             @Param("startTime") String startTime,
                                             @Param("endTime") String endTime);

    @Query(value = "select start_time from data_alarm_event a where a.dev_id=:devId and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<String> searchStartTimeByDevId(@Param("devId") Long devId,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime);

    @Query(value = "select start_time from data_alarm_event a where a.dev_id=:devId and a.abnormal_status_id=:abnormalStatusId " +
            "and a.start_time>=:startTime and a.start_time<=:endTime", nativeQuery = true)
    List<String> searchStartTimeByDevId(@Param("devId") Long devId,
                                        @Param("abnormalStatusId") Long abnormalStatusId,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime);

    @Query(value = "select dev_title from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a.start_time<=:endTime",
            nativeQuery = true)
    List<String> searchDevTitleByStationIds(@Param("stationId") List<Long> stationId,
                                            @Param("startTime") String startTime,
                                            @Param("endTime") String endTime);

    @Query(value = "select dev_title from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a.start_time<=:endTime " +
            "and a.alarm_level_id in (:alarmLevelIds)",
            nativeQuery = true)
    List<String> searchDevTitleByStationIdsAndAlarmLevel(@Param("stationId") List<Long> stationId,
                                                        @Param("startTime") String startTime,
                                                        @Param("endTime") String endTime,
                                                        @Param("alarmLevelIds") List<Long> alarmLevelIds);

    @Query(value = "select alarm_status_title from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a" +
            ".start_time<=:endTime", nativeQuery = true)
    List<String> searchAbnormalRuleTitleByStationIds(@Param("stationId") List<Long> stationId,
                                                     @Param("startTime") String startTime,
                                                     @Param("endTime") String endTime);

    @Query(value = "select alarm_status_title from data_alarm_event a where a.station_id in :stationId and a.start_time>=:startTime and a" +
            ".start_time<=:endTime and a.alarm_level_id in (:alarmLevelIds)", nativeQuery = true)
    List<String> searchAbnormalRuleTitleByStationIdsAndAlarmLevel(@Param("stationId") List<Long> stationId,
                                                                 @Param("startTime") String startTime,
                                                                 @Param("endTime") String endTime,
                                                                 @Param("alarmLevelIds") List<Long> alarmLevelIds);

    @Query(value = "select * from data_alarm_event where alarm_status_id=:ruleId and start_rtime=:startRtime limit 1", nativeQuery= true)
    AlarmEvent findSameRuleAndRtime(@Param("ruleId") Long ruleId, @Param("startRtime") Long startRtime);

    @Query(value = "select * from data_alarm_event where alarm_status_id=:ruleId and start_rtime<=:startRtime  and (process_rtime is null or " +
            "process_rtime>=:endRtime) order by start_rtime desc limit 1", nativeQuery= true)
    AlarmEvent getLastHistoryEventByStartTime(@Param("ruleId") Long ruleId, @Param("startRtime") Long startRtime, @Param("endRtime") Long endRtime);

    @Query(value = "select * from data_alarm_event where alarm_status_id=:ruleId and process_rtime>=:startRtime and process_rtime<=:endRtime order by " +
            "start_rtime desc limit 1", nativeQuery= true)
    AlarmEvent getFirstEventByRuleAndTimeRange(@Param("ruleId") Long ruleId, @Param("startRtime") Long startRtime, @Param("endRtime") Long endRtime);
}
